﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class AwardsDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoAwards model)
        {
            model.AWA_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Awards]");
            sql.Append("([AWA_ID],[AWA_NAME],[AWA_TYPE],[AWA_LEVAL])");
            sql.Append("VALUES");
            sql.Append("(@AWA_ID,@AWA_NAME,@AWA_TYPE,@AWA_LEVAL)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoAwards model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Awards] SET ");
            sql.Append("AWA_NAME=@AWA_NAME");
            sql.Append(",AWA_TYPE=@AWA_TYPE");
            sql.Append(",AWA_LEVAL=@AWA_LEVAL");
            sql.Append(" WHERE ");
            sql.Append("AWA_ID=@AWA_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string aWA_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Awards] SET ");
            sql.Append(" AWA_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("AWA_ID=@AWA_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@AWA_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = aWA_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoAwards GetModel(string aWA_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT AWA_ID,AWA_NAME,AWA_TYPE,AWA_LEVAL FROM Awards");
            sql.Append(" WHERE ");
            sql.Append("AWA_ID=@AWA_ID AND AWA_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@AWA_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = aWA_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoAwards> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoAwards> GetAllAwards()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT AWA_ID,AWA_NAME,AWA_TYPE,AWA_LEVAL FROM Awards");
            sql.Append(" where ");
            sql.Append("AWA_IsShow='true'");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoAwards> list = GetList(table);
            return list != null && list.Count > 0 ? list : null;
        }

        /// <summary>
        /// 得到校内奖项
        /// </summary>
        /// <returns></returns>
        public IList<BoAwards> GetInnerAwards()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT AWA_ID,AWA_NAME,AWA_TYPE,AWA_LEVAL FROM Awards");
            sql.Append(" where ");
            sql.Append("AWA_IsShow='true' and AWA_LEVAL='校级'  ");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoAwards> list = GetList(table);
            return list != null && list.Count > 0 ? list : null;
        }

        private IList<BoAwards> GetList(DataTable table)
        {
            List<BoAwards> list = new List<BoAwards>();
            if (table != null && table.Rows.Count > 0)
            {
               
                for (int i = 0; i < table.Rows.Count; i++)
                {
                     BoAwards model = new BoAwards();
                    model.AWA_ID = table.Rows[i]["AWA_ID"]==System.DBNull .Value ? "" :(string)table.Rows[i]["AWA_ID"];
                    model.AWA_NAME = table.Rows[i]["AWA_NAME"]==System .DBNull .Value ?"":(string)table.Rows[i]["AWA_NAME"];
                    model.AWA_TYPE = table.Rows[i]["AWA_TYPE"]==System .DBNull .Value ?0:(Int32)table.Rows[i]["AWA_TYPE"];
                    model.AWA_LEVAL = table.Rows[i]["AWA_LEVAL"]==System .DBNull .Value ?"":(string)table.Rows[i]["AWA_LEVAL"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoAwards model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@AWA_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@AWA_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@AWA_TYPE",SqlDbType.Int,4),
                new SqlParameter("@AWA_LEVAL",SqlDbType.NVarChar,50),
            };
            parameters[0].Value = model.AWA_ID;
            parameters[1].Value = model.AWA_NAME;
            parameters[2].Value = model.AWA_TYPE;
            parameters[3].Value = model.AWA_LEVAL;
            return parameters;
        }
    }
}